﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace Cashier_DataManager.data
{
    public class SQLiteDAO
    {
        private String dbPath;
        private SQLiteConnectionInstance sqliteConnectionInstance;

        public SQLiteDAO(String sqlitePath)
        {
            this.dbPath = sqlitePath;
            this.sqliteConnectionInstance = new SQLiteConnectionInstance();
        }

        /// <summary>
        /// 查询SQLite中的所有表
        /// </summary>
        /// <returns></returns>
        public DataTable GetSQLiteTables()
        {
            DataTable dt = null;
            using (SQLiteConnection conn = sqliteConnectionInstance.GetSQLiteConnection(dbPath))
            {
                conn.Open();
                String sql = "select name from sqlite_master where type='table'";
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                dt = new DataTable("sqlite_master");
                da.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 清空表数据
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public int EmptyTable(String tablename)
        {
            int count = 0;
            if (!String.IsNullOrEmpty(tablename) && !tablename.ToLower().Contains("sqlite") && !tablename.Equals("dic_chinacity"))
            {
                using (SQLiteConnection conn = sqliteConnectionInstance.GetSQLiteConnection(dbPath))
                {
                    if (!tablename.ToLower().Equals("dic_chinacity"))
                    {
                        conn.Open();
                        String sql = "delete from " + tablename;
                        SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                        count = cmd.ExecuteNonQuery();

                        cmd.CommandText = "update sqlite_sequence SET seq = 0 where name ='" + tablename + "'";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            return count;
        }

        public void ImportData(String importDBPath, String tablename)
        {
            if (!tablename.ToLower().Contains("sqlite") && !tablename.Equals("dic_chinacity"))
            {
                DataTable dt = null;
                using (SQLiteConnection conn = sqliteConnectionInstance.GetSQLiteConnection(dbPath))
                {
                    conn.Open();
                    String sql = "select * from " + tablename;
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    dt = new DataTable(tablename);
                    da.Fill(dt);
                }

                if (dt != null && dt.Rows.Count > 0)
                {
                    using (SQLiteConnection conn = sqliteConnectionInstance.GetSQLiteConnection(importDBPath))
                    {
                        conn.Open();
                        try
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                if (!dt.TableName.ToLower().Equals("dic_chinacity"))
                                {
                                    DataRow row = dt.Rows[i];
                                    StringBuilder sbCol = new StringBuilder("insert into ");
                                    sbCol.Append(dt.TableName).Append("(");
                                    StringBuilder sbVal = new StringBuilder("values(");
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        sbCol.Append(col.ColumnName).Append(",");
                                        if (row[col.ColumnName] != null)
                                        {
                                            if (col.DataType == Type.GetType("System.String") || col.DataType == Type.GetType("System.Boolean"))
                                                sbVal.Append("'").Append(row[col.ColumnName].ToString()).Append("',");
                                            else if(col.DataType == Type.GetType("System.DateTime"))
                                                sbVal.Append("'").Append(DateTime.Parse(row[col.ColumnName].ToString()).ToString("yyyy-MM-dd HH:mm:ss")).Append("',");
                                            else if (col.DataType == Type.GetType("System.Int16") || col.DataType == Type.GetType("System.Int32") || col.DataType == Type.GetType("System.Int64") ||
                                                col.DataType == Type.GetType("System.Decimal") || col.DataType == Type.GetType("System.Double") || col.DataType == Type.GetType("System.Boolean"))
                                                sbVal.Append(row[col.ColumnName].ToString()).Append(",");
                                        }
                                        else
                                        {
                                            sbVal.Append("null,");
                                        }
                                    }
                                    sbCol.Remove(sbCol.Length - 1, 1);
                                    sbVal.Remove(sbVal.Length - 1, 1);
                                    sbCol.Append(") ").Append(sbVal.ToString()).Append(")");
                                    Console.WriteLine(sbCol.ToString());

                                    SQLiteCommand cmd = new SQLiteCommand(sbCol.ToString(), conn);
                                    int flag = cmd.ExecuteNonQuery();
                                    if (flag > 0)
                                    {
                                        Console.WriteLine("success");
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                }
            }
        }
    }
}
